﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using CS2102_Music.Logic.LogicModels;

namespace CS2102_Music.Logic
{
    public partial class Logic
    {
        // Functions with SQL
        public List<SongL> GetNewSongs()
        {
            // get songs
            List<SongL> newSongs = new List<SongL>();
            SongL song = new SongL();
            string query = "SELECT * FROM song_owned_by s WHERE s.active = 'Y' ORDER BY s.releasedate DESC, s.title LIMIT 10";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                song.ID = result.GetInt32(0);
                song.Title = result.GetString(1);
                song.ReleaseDate = result.GetDateTime(2);  
                song.Price = Math.Round((result.GetDouble(3)),2);
                song.Genre = result.GetString(4);
                song.Composer = result.GetString(5);
                song.Lyricist = result.GetString(6);
                song.Duration = result.GetInt32(7);
                song.Active = result.GetString(8);

                newSongs.Add(song);

                song = new SongL();
            }

            result.Close();

            // get artists for each song
            string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < newSongs.Count(); i++)
            {
                songID = newSongs[i].ID;
                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                               , new QueryParameter("@songID", songID.ToString())
                                                                               );

                while (result2.Read())
                {
                    artist.Name = result2.GetString(0);
                    artist.Photo = result2.GetString(1);
                    artist.ID = result2.GetInt32(2);

                    newSongs[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result2.Close();
            }

            return newSongs;
        }
        public SongL GetSongByID(int songID)
        {
            // get song
            SongL song = new SongL();
            string query = "SELECT * FROM song_owned_by s WHERE s.active = 'Y' AND s.songID = @songID;";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query
                                                                          , new QueryParameter("@songID", songID.ToString())
                                                                          );

            while (result.Read())
            {
                song.ID = result.GetInt32(0);
                song.Title = result.GetString(1);
                song.ReleaseDate = result.GetDateTime(2);
                song.Price = Math.Round((result.GetDouble(3)), 2);
                song.Genre = result.GetString(4);
                song.Composer = result.GetString(5);
                song.Lyricist = result.GetString(6);
                song.Duration = result.GetInt32(7);
                song.Active = result.GetString(8);
            }

            result.Close();

            // get artists for each song
            string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            ArtistL artist = new ArtistL();

            MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                            , new QueryParameter("@songID", songID.ToString())
                                                                            );

            while (result2.Read())
            {
                artist.Name = result2.GetString(0);
                artist.Photo = result2.GetString(1);
                artist.ID = result2.GetInt32(2);

                song.Artists.Add(artist);

                artist = new ArtistL();
            }

            result2.Close();
        

            return song;
        }
        public bool CheckUserBoughtSong(string user, int songID)
        {
            bool exist = false;
            int count = 0;
            string query = "SELECT COUNT(*) FROM buysong bs WHERE bs.buyer = @userEmail AND bs.song = @songID";
            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query
                                                                          , new QueryParameter("@userEmail", user)
                                                                          , new QueryParameter("@songID", songID.ToString())
                                                                          );
            while (result.Read())
            {
                count = result.GetInt32(0);
            }

            result.Close();

            if (count == 0)
                exist = false;
            else
                exist = true;

            return exist;
        }
        public void BuySong(string user, int songID, DateTime date, double price)
        {
            string query = "INSERT INTO buysong VALUES(@user, @songID, " + Utilities.ToSQLDateString(date) + ", @price, 0)";
            bool test = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@user", user)
                                               , new QueryParameter("@songID", songID.ToString())
                                               , new QueryParameter("@price", price.ToString())
                                              );
        }
        public void UpdateSong(int songID, string title, string releaseDate, double price, string genre, string composer, string lyricist, int duration, string[] artists, bool changeArtists)
        {
            string query = "UPDATE song_owned_by SET title = @title WHERE songID = @songID";

            bool update1 = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@title", title)
                                               , new QueryParameter("@songID", songID.ToString())
                                              );

            query = "UPDATE song_owned_by SET releaseDate = @releaseDate WHERE songID = @songID";
            bool update2 = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@releaseDate", releaseDate)
                                               , new QueryParameter("@songID", songID.ToString())
                                              );

            query = "UPDATE song_owned_by SET price = @price WHERE songID = @songID";
            bool update3 = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@price", price.ToString())
                                               , new QueryParameter("@songID", songID.ToString())
                                              );
            query = "UPDATE song_owned_by SET genre = @genre WHERE songID = @songID";
            bool update4 = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@genre", genre)
                                               , new QueryParameter("@songID", songID.ToString())
                                              );

            query = "UPDATE song_owned_by SET composer = @composer WHERE songID = @songID";
            bool update5 = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@composer", composer)
                                               , new QueryParameter("@songID", songID.ToString())
                                              );

            query = "UPDATE song_owned_by SET lyricist = @lyricist WHERE songID = @songID";
            bool update6 = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@lyricist", genre)
                                               , new QueryParameter("@songID", songID.ToString())
                                              );

            query = "UPDATE song_owned_by SET duration = @duration WHERE songID = @songID";
            bool update7 = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@duration", duration.ToString())
                                               , new QueryParameter("@songID", songID.ToString())
                                              );

            if (changeArtists)
            {
                //artist
                //remove all first
                query = "DELETE FROM sing WHERE song = @songID";
                bool removal = databaseConnector.ExecuteNonQuery(query
                                                   , new QueryParameter("@songID", songID.ToString())
                                                  );

                //insert
                string artistID;
                for (int i = 0; i < artists.Count(); i++)
                {
                    artistID = artists[i];
                    query = "INSERT INTO sing VALUES(@artistID, @songID)";
                    bool insert = databaseConnector.ExecuteNonQuery(query
                                                       , new QueryParameter("@artistID", artistID)
                                                       , new QueryParameter("@songID", songID.ToString())
                                                      );
                }
            }


        }
        public bool DeleteSong(int songID)
        {
            string query = "UPDATE song_owned_by SET active = 'N' WHERE songID = @songID";

            bool delete = databaseConnector.ExecuteNonQuery(query
                                               , new QueryParameter("@songID", songID.ToString())
                                              );
            return delete;
        }
        public List<SongL> GetSongsNotInAlbum(int albumID)
        {
            List<SongL> songsNotInAlbum = new List<SongL>();
            SongL song = new SongL();

            // get songs of those not in album
            string query1 = "SELECT * FROM song_owned_by sob WHERE sob.active = 'Y' AND sob.songID NOT IN (SELECT song  FROM recorded_in ri WHERE ri.album = @albumID) ORDER BY sob.title";
            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query1
                                                                         , new QueryParameter("@albumID", albumID.ToString())
                                                                         );

            while (result1.Read())
            {
                song.ID = result1.GetInt32(0);
                song.Title = result1.GetString(1);
                song.ReleaseDate = result1.GetDateTime(2);
                song.Price = result1.GetDouble(3);
                song.Genre = result1.GetString(4);
                song.Composer = result1.GetString(5);
                song.Lyricist = result1.GetString(6);
                song.Duration = result1.GetInt32(7);
                song.Active = result1.GetString(8);

                songsNotInAlbum.Add(song);

                song = new SongL();
            }

            result1.Close();

            // get artists for each song
            string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < songsNotInAlbum.Count(); i++)
            {
                songID = songsNotInAlbum[i].ID;

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                                , new QueryParameter("@songID", songID.ToString())
                                                                                );

                while (result2.Read())
                {
                    artist.Name = result2.GetString(0);
                    artist.Photo = result2.GetString(1);
                    artist.ID = result2.GetInt32(2);

                    songsNotInAlbum[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result2.Close();
            }

            return songsNotInAlbum;
        }
        public List<SongL> GetAllSongs()
        {
            List<SongL> songs = new List<SongL>();
            SongL song = new SongL();

            // get songs of those not in album
            string query = "SELECT * FROM displaySongs";
            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                song.ID = result.GetInt32(0);
                song.Title = result.GetString(1);
                song.ReleaseDate = result.GetDateTime(2);
                song.Price = Math.Round((result.GetDouble(3)),2);
                song.Genre = result.GetString(4);
                song.Composer = result.GetString(5);
                song.Lyricist = result.GetString(6);
                song.Duration = result.GetInt32(7);
                song.Active = result.GetString(8);

                songs.Add(song);

                song = new SongL();
            }

            result.Close();


            // get artists for each song
            string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < songs.Count(); i++)
            {
                songID = songs[i].ID;

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                                , new QueryParameter("@songID", songID.ToString())
                                                                                );

                while (result2.Read())
                {
                    artist.Name = result2.GetString(0);
                    artist.Photo = result2.GetString(1);
                    artist.ID = result2.GetInt32(2);

                    songs[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result2.Close();
            }


            return songs;
        }
        public void CreateSong(string title, string releaseDate, double price, string genre, string composer, string lyricist, int duration, string[] artists)
        {
            int newSongID = 0;
            // get next ID for song
            string query = "SELECT COUNT(*) FROM song_owned_by";
            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);

            while (result.Read())
            {
                newSongID = result.GetInt32(0);
            }

            result.Close();

            // insert song into song table
            query = "CALL createSong (@songID, @title, @releaseDate, @price, @genre, @composer, @lyricist, @duration, 'Y')";

            databaseConnector.ExecuteNonQuery(query
                                              , new QueryParameter("@songID", newSongID.ToString())
                                              , new QueryParameter("@title", title)
                                              , new QueryParameter("@releaseDate", releaseDate)
                                              , new QueryParameter("@price", price.ToString())
                                              , new QueryParameter("@genre", genre)
                                              , new QueryParameter("@composer", composer)
                                              , new QueryParameter("@lyricist", lyricist)
                                              , new QueryParameter("@duration", duration.ToString())
                                              );

            // insert into sing table to link song w artist
            query = "INSERT INTO sing VALUES(@artistID, @songID)";

            for (int i = 0; i < artists.Count(); i++)
            {
                databaseConnector.ExecuteNonQuery(query
                                              , new QueryParameter("@songID", newSongID.ToString())
                                              , new QueryParameter("@artistID", artists[i])
                                              );
            }
        }
        public List<SongL> WorstSellingSong(out int number)
        {
            SongL song = new SongL();
            number = 0;
            List<SongL> worst = new List<SongL>();

            // those that no one bought yet.
            string query1 = "SELECT s1.songID, s1.title, s1.price, s1.releaseDate, s1.genre, s1.composer, s1.lyricist, s1.duration, 0 FROM song_owned_by s1 WHERE s1.active = 'Y' AND NOT EXISTS (SELECT * FROM buysong bs WHERE s1.songID = bs.song GROUP BY s1.songID, s1.title) ORDER BY s1.title, s1.songID";

            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query1);

            while (result1.Read())
            {
                song.ID = result1.GetInt32(0);
                song.Title = result1.GetString(1);
                song.Price = Math.Round((result1.GetDouble(2)), 2);
                song.ReleaseDate = result1.GetDateTime(3);
                song.Genre = result1.GetString(4);
                song.Composer = result1.GetString(5);
                song.Lyricist = result1.GetString(6);
                song.Duration = result1.GetInt32(7);

                number = result1.GetInt32(8);

                worst.Add(song);
                song = new SongL();
            }

            result1.Close();

            // check if all songs are sold -> if yes, find the least sold
            if (worst.Count() == 0)
            {
                string query2 = "SELECT s.songID, s.title, s.price, s.releaseDate, s.genre, s.composer, s.lyricist, s.duration, COUNT(*) FROM song_owned_by s, buysong bs WHERE s.songID = bs.song AND s.active = 'Y' GROUP BY s.songID, s.title, s.price, s.releaseDate, s.genre, s.composer, s.lyricist, s.duration HAVING COUNT(*) <= ALL(SELECT COUNT(*) FROM song_owned_by s2, buysong bs2 WHERE s2.songID = bs2.song AND s2.active = 'Y' GROUP BY s2.songID, s2.title) ORDER BY s.title, s.songID";

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2);

                while (result2.Read())
                {
                    song.ID = result1.GetInt32(0);
                    song.Title = result1.GetString(1);
                    song.Price = Math.Round((result1.GetDouble(2)), 2);
                    song.ReleaseDate = result1.GetDateTime(3);
                    song.Genre = result1.GetString(4);
                    song.Composer = result1.GetString(5);
                    song.Lyricist = result1.GetString(6);
                    song.Duration = result1.GetInt32(7);

                    number = result1.GetInt32(8);

                    worst.Add(song);
                    song = new SongL();
                }

                result2.Close();
            }


            // get artists for each song
            string query3 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < worst.Count(); i++)
            {
                songID = worst[i].ID;

                MySqlDataReader result3 = databaseConnector.ExecuteReaderQuery(query3
                                                                                , new QueryParameter("@songID", songID.ToString())
                                                                                );

                while (result3.Read())
                {
                    artist.Name = result3.GetString(0);
                    artist.Photo = result3.GetString(1);
                    artist.ID = result3.GetInt32(2);

                    worst[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result3.Close();
            }

            return worst;
        }
        public List<SongL> GetDeletedSongs()
        {
            List<SongL> deleted = new List<SongL>();
            SongL song = new SongL();

            string query = "SELECT * FROM song_owned_by s WHERE s.active = 'N' ORDER BY s.title, s.songID";


            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query);

            while (result1.Read())
            {
                song.ID = result1.GetInt32(0);
                song.Title = result1.GetString(1);
                song.ReleaseDate = result1.GetDateTime(2);
                song.Price = Math.Round((result1.GetDouble(3)),2);
                song.Genre = result1.GetString(4);
                song.Composer = result1.GetString(5);
                song.Lyricist = result1.GetString(6);
                song.Duration = result1.GetInt32(7);
                song.Active = result1.GetString(8);

                deleted.Add(song);
                song = new SongL();
            }

            result1.Close();


            // get artists of each song
            string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < deleted.Count(); i++)
            {
                songID = deleted[i].ID;

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                                , new QueryParameter("@songID", songID.ToString())
                                                                                );

                while (result2.Read())
                {
                    artist.Name = result2.GetString(0);
                    artist.Photo = result2.GetString(1);
                    artist.ID = result2.GetInt32(2);

                    deleted[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result2.Close();
            }


            return deleted;
        }
        public List<SongL> SearchSong(string search, string filter)
        {
            List<SongL> list = new List<SongL>();
            SongL song = new SongL();
            string query = "";
            if(filter == "Title")
            {
                query = "SELECT * FROM song_owned_by sob WHERE sob.title like @song AND sob.active = 'Y' ORDER BY sob.title";
                MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query
                                                                                , new QueryParameter("@song", "%" + search + "%")
                                                                                );

                while (result1.Read())
                {
                    song.ID = result1.GetInt32(0);
                    song.Title = result1.GetString(1);
                    song.ReleaseDate = result1.GetDateTime(2);
                    song.Price = Math.Round((result1.GetDouble(3)), 2);
                    song.Genre = result1.GetString(4);
                    song.Composer = result1.GetString(5);
                    song.Lyricist = result1.GetString(6);
                    song.Duration = result1.GetInt32(7);
                    song.Active = result1.GetString(8);

                    list.Add(song);
                    song = new SongL();
                }

                result1.Close();
            }
            if (filter == "Artist")
            {
                query = "SELECT * FROM song_owned_by sob, sing s, artist a WHERE sob.songID = s.song AND sob.active = 'Y' AND a.artistID = s.artist AND a.name like @artist ORDER BY a.name, sob.title";
               
                MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query
                                                                               , new QueryParameter("@artist", "%" + search + "%")
                                                                                );

                while (result1.Read())
                {
                    song.ID = result1.GetInt32(0);
                    song.Title = result1.GetString(1);
                    song.ReleaseDate = result1.GetDateTime(2);
                    song.Price = Math.Round((result1.GetDouble(3)), 2);
                    song.Genre = result1.GetString(4);
                    song.Composer = result1.GetString(5);
                    song.Lyricist = result1.GetString(6);
                    song.Duration = result1.GetInt32(7);
                    song.Active = result1.GetString(8);

                    list.Add(song);
                    song = new SongL();
                }

                result1.Close();

            }

             if (filter == "Genre")
            {
                query = "SELECT * FROM song_owned_by sob WHERE sob.active = 'Y' AND sob.genre like @genre ORDER BY sob.genre, sob.title";
               
                MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query
                                                                                , new QueryParameter("@genre", "%" + search + "%")
                                                                                );

                while (result1.Read())
                {
                    song.ID = result1.GetInt32(0);
                    song.Title = result1.GetString(1);
                    song.ReleaseDate = result1.GetDateTime(2);
                    song.Price = Math.Round((result1.GetDouble(3)), 2);
                    song.Genre = result1.GetString(4);
                    song.Composer = result1.GetString(5);
                    song.Lyricist = result1.GetString(6);
                    song.Duration = result1.GetInt32(7);
                    song.Active = result1.GetString(8);

                    list.Add(song);
                    song = new SongL();
                }

                result1.Close();

            }


            // get all artists of that songs
             // get artists of each song
            string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < list.Count(); i++)
            {
                songID = list[i].ID;

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                                , new QueryParameter("@songID", songID.ToString())
                                                                                );

                while (result2.Read())
                {
                    artist.Name = result2.GetString(0);
                    artist.Photo = result2.GetString(1);
                    artist.ID = result2.GetInt32(2);

                    list[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result2.Close();
            }


            return list;
        }
        public List<SongL> HottestSong()
        {
            List<SongL> list = new List<SongL>();
            SongL song = new SongL();
            string query = "";
    
                query = "SELECT s.songID, s.title, s.price, s.releaseDate, s.genre, s.composer, s.lyricist, s.duration, COUNT(*) FROM song_owned_by s, buysong bs WHERE s.songid = bs.song AND s.active = 'Y' GROUP BY s.songID, s.title, s.price, s.releaseDate, s.genre, s.composer, s.lyricist, s.duration HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM song_owned_by s2, buysong bs2 WHERE s2.songid = bs2.song AND s2.active = 'Y' GROUP BY s2.songid, s2.title) ORDER BY s.title, s.songID";

                MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query);
                                                                                

                while (result1.Read())
                {
                    song.ID = result1.GetInt32(0);
                    song.Title = result1.GetString(1);
                    song.Price = Math.Round((result1.GetDouble(2)), 2);
                    song.ReleaseDate = result1.GetDateTime(3);             
                    song.Genre = result1.GetString(4);
                    song.Composer = result1.GetString(5);
                    song.Lyricist = result1.GetString(6);
                    song.Duration = result1.GetInt32(7);
                    song.Active = result1.GetString(8);

                    list.Add(song);
                    song = new SongL();
                }

                result1.Close();


            // get all artists of that songs
            // get artists of each song
            string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < list.Count(); i++)
            {
                songID = list[i].ID;

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                                , new QueryParameter("@songID", songID.ToString())
                                                                                );

                while (result2.Read())
                {
                    artist.Name = result2.GetString(0);
                    artist.Photo = result2.GetString(1);
                    artist.ID = result2.GetInt32(2);

                    list[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result2.Close();
            }


            return list;
        }
        public List<SongL> BestSellersSongs()
        {
            List<SongL> list = new List<SongL>();
            SongL song = new SongL();
            string query = "";

            query = "SELECT s.songID, s.title, s.price, s.releaseDate, s.genre, s.composer, s.lyricist, s.duration, COUNT(*) FROM song_owned_by s, buysong bs WHERE s.songid = bs.song AND s.active = 'Y' GROUP BY s.songID, s.title, s.price, s.releaseDate, s.genre, s.composer, s.lyricist, s.duration ORDER BY COUNT(*) DESC, s.title, s.songID LIMIT 10";

            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query);


            while (result1.Read())
            {
                song.ID = result1.GetInt32(0);
                song.Title = result1.GetString(1);
                song.Price = Math.Round((result1.GetDouble(2)), 2);
                song.ReleaseDate = result1.GetDateTime(3);
                song.Genre = result1.GetString(4);
                song.Composer = result1.GetString(5);
                song.Lyricist = result1.GetString(6);
                song.Duration = result1.GetInt32(7);

                list.Add(song);
                song = new SongL();
            }

            result1.Close();


            // get all artists of that songs
            // get artists of each song
            string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
            int songID;
            ArtistL artist = new ArtistL();

            for (int i = 0; i < list.Count(); i++)
            {
                songID = list[i].ID;

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                                , new QueryParameter("@songID", songID.ToString())
                                                                                );

                while (result2.Read())
                {
                    artist.Name = result2.GetString(0);
                    artist.Photo = result2.GetString(1);
                    artist.ID = result2.GetInt32(2);

                    list[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result2.Close();
            }


            return list;
        }
    }
}